package com.xian.jdbc;


import java.io.FileOutputStream;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 获取某个库所有表的建表语句
 */
public class GetCreateSql {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String db = "db1";
        Connection con = DriverManager.getConnection("jdbc:mysql://r71/"+db, "root", "123456");
        Statement st = con.createStatement();
        //1.获取库所有表名
        ResultSet rs = st.executeQuery("show tables");
        List<String> tableList = new ArrayList<String>();
        while (rs.next()){
            String table = rs.getString(1);
            System.out.println(table);
            tableList.add(table);
        }
        rs.close();

        //2.遍历表，获取具体表的建表语句
        StringBuilder sb = new StringBuilder();
        for(String table : tableList){
            ResultSet resultSet = st.executeQuery("show create table " + table);
            if(resultSet.first()){
                String createTableSql = resultSet.getString(2);
                String createTableWithDbSql = createTableSql.replace("CREATE TABLE `", "CREATE TABLE "+db+".`");
                //System.out.println(createTableSql);
                //System.out.println(createTableWithDbSql+";");
                sb.append(createTableWithDbSql);

            }
            sb.append(";\n\n\n"); //在表最后加逗号，每个表之间间隔N行
        }

        st.close();
        con.close();

        //3.打印最后整个库的建表语句
        //System.out.println("=========");
        System.out.println(sb);

        //4.写到一个文件中
        FileWriter fileWriter = new FileWriter("e:/test/creatTable.sql");
        //FileWriter fileWriter = new FileWriter("/root/creatTable.sql");
        fileWriter.write(sb.toString());
        fileWriter.close();

    }
}
